"""
模拟浏览器行为，获取东方财富的股票的历史主力资金信息
"""
"""
pip install selenium
pip install beautifulsoup4
pip install pymysql,lxml
"""

from selenium import webdriver
import time, datetime
from bs4 import BeautifulSoup
import pymysql

browser = webdriver.Chrome('chromedriver3.exe')
url_today = 'http://data.eastmoney.com/zjlx/detail.html'
# 进入网站
browser.get(url_today)

# 建表mysql
"""
drop table if exists stock.stock_history_money_d;
CREATE TABLE         stock.stock_history_money_d
(
id                              bigint auto_increment comment '自增id'                           ,
code                            varchar(10)           comment '代码'                             ,
name                            varchar(10)           comment '名称'                             ,
day_no                          varchar(10)           comment '日期'                             ,
price                           double(11,2)          comment '收盘价'                           ,
change_rate                     double(11,2)          comment '涨跌幅'                           ,
main_money_num                  bigint                comment '主力净额'                         ,
main_money_rate                 double(11,2)          comment '主力净占比'                       ,
super_big_money_num             bigint                comment '超大单净额'                       ,
super_big_money_rate            double(11,2)          comment '超大单净占比'                     ,
big_money_num                   bigint                comment '大单净额'                         ,
big_money_rate                  double(11,2)          comment '大单净占比'                       ,
medium_money_num                bigint                comment '中单净额'                         ,
medium_money_rate               double(11,2)          comment '中单净占比'                       ,
small_money_num                 bigint                comment '小单净额'                         ,
small_money_rate                double(11,2)          comment '小单净占比'                       ,
load_time                       timestamp             comment '更新时间'                         ,
PRIMARY KEY (id),
key(day_no),
key(code),
key(name)
)
ENGINE = INNODB
DEFAULT CHARSET = utf8 COMMENT = '股票历史资金流入情况'
;
"""


def change_unit(num):
    if num[-1] == '亿':
        return float(num[:-1]) * 100000000
    elif num[-1] == '万':
        return float(num[:-1]) * 10000
    elif num == '' or num == ' ' or num == '-':
        return 0
    else:
        return int(num[:-3])


# 本机
conn1 = pymysql.connect(host='localhost', user='root', passwd='root', db='stock', charset="utf8")
# 阿里云试用
conn3 = pymysql.connect(host='rm-2vc83ot65e551h73beo.mysql.cn-chengdu.rds.aliyuncs.com', port=3306, user='river',
                        passwd='lihua201314SJ', db='stock', charset="utf8")

conn = conn3
cursor = conn.cursor()

day_no = str(datetime.date.today()-datetime.timedelta(days=0))

PAGE_MAX = 99

page = 1

if page > 1:
    browser.find_element_by_id('gotopageindex').clear()
    browser.find_element_by_id('gotopageindex').send_keys(page)
    # <input type="submit" class="btn" value="Go">
    browser.find_element_by_xpath("//*[@value='Go']").click()
    time.sleep(2)

while True:
    print(f'第{page}页')
    try:
        # 使用beautifulSoup解析网页标签
        html = BeautifulSoup(browser.page_source, "lxml")
        money_info_today = html.select("#dataview .dataview-body tbody tr")
        for item in money_info_today:
            item_list = item.find_all('td')
            item_list_clean = [str(i.text).strip().replace(' ', '') for i in item_list]
            code = item_list_clean[1]
            # 解析字段
            name = item_list_clean[2]
            price = 0 if item_list_clean[4] == '-' else item_list_clean[4]
            change_rate = 0 if item_list_clean[5] == '-' else item_list_clean[5][:-1]
            main_money_num = change_unit(item_list_clean[6])
            main_money_rate = 0 if item_list_clean[7] == '-' else item_list_clean[7][:-1]
            super_big_money_num = change_unit(item_list_clean[8])
            super_big_money_rate = 0 if item_list_clean[9] == '-' else item_list_clean[9][:-1]
            big_money_num = change_unit(item_list_clean[10])
            big_money_rate = 0 if item_list_clean[11] == '-' else item_list_clean[11][:-1]
            medium_money_num = change_unit(item_list_clean[12])
            medium_money_rate = 0 if item_list_clean[13] == '-' else item_list_clean[13][:-1]
            small_money_num = change_unit(item_list_clean[14])
            small_money_rate = 0 if item_list_clean[15] == '-' else item_list_clean[15][:-1]

            # 检查库中是否有该天的数据
            sql_check = f"select * from stock.stock_history_money_d where code='{code}' and day_no='{day_no}'"
            res = cursor.execute(sql_check)
            if res == 1:
                # 更新
                sql = f"""
                update stock.stock_history_money_d
                set 
                price                    = {price}                 ,
                change_rate              = {change_rate}           ,
                main_money_num           = {main_money_num}        ,
                super_big_money_num      = {super_big_money_num}   ,
                big_money_num            = {big_money_num}         ,
                medium_money_num         = {medium_money_num}      ,
                small_money_num          = {small_money_num}       ,
                main_money_rate          = {main_money_rate}       ,
                super_big_money_rate     = {super_big_money_rate}  ,
                big_money_rate           = {big_money_rate}        ,
                medium_money_rate        = {medium_money_rate}     ,
                small_money_rate         = {small_money_rate}      ,
                load_time                = current_timestamp       
                where code='{code}' and day_no='{day_no}'
                """
            else:
                # 插入数据
                sql = f"""
                insert into stock.stock_history_money_d
                      (code,name,day_no,price,change_rate,main_money_num,super_big_money_num,big_money_num,medium_money_num,small_money_num,main_money_rate,super_big_money_rate,big_money_rate,medium_money_rate,small_money_rate,load_time)
                values('{code}','{name}','{day_no}',{price},{change_rate},{main_money_num},{super_big_money_num},{big_money_num},{medium_money_num},{small_money_num},{main_money_rate},{super_big_money_rate},{big_money_rate},{medium_money_rate},{small_money_rate},current_timestamp)
                """
            cursor.execute(sql)
        conn.commit()
        time.sleep(0.5)
        if page >= PAGE_MAX:
            break
        if page % 10 == 0:
            time.sleep(3)
        if page % 20 == 0:
            time.sleep(5)
        page = page + 1
        browser.find_element_by_link_text('下一页').click()
        time.sleep(1.5)
    except Exception as e:
        print('失败：', e)
        # print(sql)
        browser.refresh()
        time.sleep(2)
        browser.find_element_by_id('gotopageindex').clear()
        browser.find_element_by_id('gotopageindex').send_keys(page)
        # <input type="submit" class="btn" value="Go">
        browser.find_element_by_xpath("//*[@value='Go']").click()
        time.sleep(2)
        continue
cursor.close()
conn.close()
